package com.xinthe.krisco.service;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import com.xinthe.krisco.domain.OrderTo;
import com.xinthe.krisco.utils.DatabaseUtil;

public class OMSDatabaseService {

	/**
	 * @ Neyaz
	 */

	public static List<String> getItemIDFromInputXML(String xmlInputValue) {
		List<String> itemValueFromXML = null;
		try {
			DocumentBuilderFactory builderFactory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = builderFactory.newDocumentBuilder();
			Document documet = builder.parse(xmlInputValue);
			NodeList nodeList = documet.getElementsByTagName("Item");

			for (int i = 0; i < nodeList.getLength(); i++) {
				Node node = nodeList.item(i);
				Element element = (Element) node;
				itemValueFromXML = new ArrayList<String>();
				itemValueFromXML.add(element.getAttribute("CountryOfOrigin"));
				itemValueFromXML.add(element.getAttribute("UnitOfMeasure"));
				itemValueFromXML.add(element.getAttribute("ProductClass"));
				itemValueFromXML.add(element.getAttribute("ItemID"));
				itemValueFromXML.add(element.getAttribute("Quantity"));
			}

		} catch (ParserConfigurationException e) {
			e.printStackTrace();
		} catch (SAXException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return itemValueFromXML;
	}

	public static List<String> getItemIDFromDatabase() {

		List<String> listDatabaseField = null;
		Connection connection = null;
		Statement stmt = null;
		ResultSet resultSet = null;

		try {
			connection = DatabaseUtil.getConnection();
			stmt = connection.createStatement();
			String sqlRefrance = "SELECT * FROM yfs_order_line";
			resultSet = stmt.executeQuery(sqlRefrance);

			if (resultSet.next()) {
				listDatabaseField = new ArrayList<String>();

				listDatabaseField
						.add(resultSet.getString("ITEM_ID").toString());
				listDatabaseField.add(resultSet.getString("UNIT_PRICE"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return listDatabaseField;
	}

	public static OrderTo getItemId() {
		List<String> itemValueFromXML = getItemIDFromInputXML("inputfiles/input.xml");

		if (itemValueFromXML.get(3).equals("")) {
			System.out.println("please provide the correct itemID");
			return null;
		}

		List<String> listDatabaseField = getItemIDFromDatabase();

		if (listDatabaseField.get(0).trim().equals(itemValueFromXML.get(3))) {

			System.out.println("item is validated in xml as well as Database");

			OrderTo orderTo = new OrderTo();
			orderTo.setCountryOfOrigin(itemValueFromXML.get(0));
			orderTo.setUnitOfMeasure(itemValueFromXML.get(1));
			orderTo.setProductClass(itemValueFromXML.get(2));
			orderTo.setItem(itemValueFromXML.get(3));
			orderTo.setQuantity(itemValueFromXML.get(4));

			orderTo.setUop(listDatabaseField.get(1));
			System.out.println(orderTo.getItem());
			System.out.println(orderTo.getCountryOfOrigin());
			System.out.println(orderTo.getProductClass());
			System.out.println(orderTo.getQuantity());
			System.out.println("Unit Prise Of ItemId Team1_eagle"
					+ "           " + orderTo.getUop());
			return orderTo;
		} else {
			System.out.println("item not exist in Database");
		}

		return null;
	}

	public static void main(String args[]) {

		getItemId();
	}

}